﻿INSERT INTO dbo.Base_UserRelation
        (UserRelationId,UserId,Category,ObjectId,IsDefault,SortCode,CreateDate         )
SELECT NEWID(),bu.UserId,2,bu.RoleId,1,0,GETDATE() FROM dbo.Base_User bu
WHERE LEN(Z__DOCTORCODE)>0
AND NOT EXISTS(
	SELECT 1 FROM dbo.Base_UserRelation b1
	WHERE b1.UserId=bu.UserId
)

Go


--问诊开药记录
IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE object_id=OBJECT_ID('ActionPrescriptionRecord') AND name='_PK')
ALTER TABLE ActionPrescriptionRecord
ADD [_PK] NVARCHAR(500) NULL
GO


IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE object_id=OBJECT_ID('InterrogationReports') AND name='_PK')
ALTER TABLE InterrogationReports
ADD [_PK] NVARCHAR(500) NULL
GO

INSERT INTO dbo.ActionPrescriptionRecord
(Id,CaseID,PatientMainId,MaterialId,MaterialName,UseId,DaysOfTaking,
EachDose,EachDoseUnit,DemandNum,PackingUnitId,Total,
UnitPrice,TotalQty,TotalPrice,Enabled,
CreateUserId,CreateUserName,OrganizeId,Currency)
SELECT  NEWID(),ir.CaseId,ir.PatientMainId,me.Id,me.Name,NULL,CASE WHEN ISNUMERIC( p.day)=1 THEN  CONVERT(INT, p.DAY) ELSE 1 END
,p.QTY,p.UNIT,0,'00000000-0000-0000-0000-000000000000',p.QTY
,p.PRICE,p.QTY,p.PRICE,0
,'neo','neo',b1.OrganizeId,ISNULL( me.CurrencyType,'00000000-0000-0000-0000-000000000000')
FROM [CS6].dbo.PRESCRIPTIONRECORDTABLE p
JOIN [CS6].dbo.MEDICALRECORDTABLE m ON (m.P_CODE=p.P_CODE AND m.TIMESTAMP=p.TIMESTAMP AND m.CLINICCODE=p.CLINICCODE)
JOIN dbo.InterrogationReports ir ON m.P_CODE+CONVERT(NVARCHAR(200),m.TIMESTAMP,21)+ m.CLINICCODE  Collate DATABASE_DEFAULT =ir.[_PK]
JOIN dbo.Material me ON (me.MaterialType=0 AND p.D_NAME  Collate DATABASE_DEFAULT=me.EnCode)
LEFT JOIN dbo.Base_DataItemDetail d1 ON p.UNIT Collate DATABASE_DEFAULT =d1.ItemValue
LEFT  JOIN dbo.Base_Organize b1 ON p.CLINICCODE Collate DATABASE_DEFAULT =b1.EnCode
WHERE
NOT exists (
SELECT 1 FROM dbo.ActionPrescriptionRecord ap
WHERE p.P_CODE+CONVERT(NVARCHAR(200),p.TIMESTAMP,21)+p.CLINICCODE Collate DATABASE_DEFAULT=ap.[_PK]
)
Go

--病人拓展资料
IF NOT EXISTS(SELECT 1 FROM SYS.COLUMNS WHERE object_id=OBJECT_ID('PatientMain') AND name='Z__FHISTORY')
ALTER TABLE dbo.PatientMain
ADD Z__FHISTORY NVARCHAR(MAX) NULL
Go

UPDATE dbo.PatientMain
SET 
DetailedAddress=		  ISNULL( (
		SELECT TOP 1  Data FROM [CS6].dbo.PATIENTEXTENDTABLE pe
		WHERE pe.P_CODE Collate DATABASE_DEFAULT=dbo.PatientMain.Code AND pe.FIELDTYPE='HADDRESS'
		),PatientMain.DetailedAddress)
,
BusDetailedAddress=  ISNULL( (
		SELECT TOP 1  Data FROM [CS6].dbo.PATIENTEXTENDTABLE pe
		WHERE pe.P_CODE Collate DATABASE_DEFAULT=dbo.PatientMain.Code 
		AND pe.FIELDTYPE='BADDRESS'
		),PatientMain.DetailedAddress)
,REMARKS=ISNULL((
		SELECT TOP 1  Data FROM [CS6].dbo.PATIENTEXTENDTABLE pe
		WHERE pe.P_CODE Collate DATABASE_DEFAULT=dbo.PatientMain.Code 
		AND pe.FIELDTYPE='REMARKS'
		),PatientMain.DetailedAddress)
,MOME=ISNULL((
		SELECT TOP 1  Data FROM [CS6].dbo.PATIENTEXTENDTABLE pe
		WHERE pe.P_CODE Collate DATABASE_DEFAULT=dbo.PatientMain.Code 
		AND pe.FIELDTYPE='PLAN'
		),PatientMain.DetailedAddress)
,Z__FHISTORY=ISNULL((
		SELECT TOP 1  Data FROM [CS6].dbo.PATIENTEXTENDTABLE pe
		WHERE pe.P_CODE Collate DATABASE_DEFAULT=dbo.PatientMain.Code 
		AND pe.FIELDTYPE='FHISTORY'
		),PatientMain.DetailedAddress)
WHERE EXISTS(
	SELECT 1 FROM [CS6].dbo.PATIENTEXTENDTABLE pe
	WHERE pe.P_CODE Collate DATABASE_DEFAULT=dbo.PatientMain.Code
)

Go


IF NOT EXISTS(SELECT 1 FROM SYS.COLUMNS WHERE object_id=OBJECT_ID('Base_Organize') AND name='_PK')
ALTER TABLE Base_Organize
ADD [_PK] NVARCHAR(500) NULL
Go

--默认给System所有新增诊所权限
INSERT INTO dbo.Base_AuthorizeData
        (AuthorizeDataId,AuthorizeType,Category,ObjectId,
         ResourceId,IsRead,SortCode,CreateDate)
SELECT NEWID(),-5,5,'system',OrganizeId,0,99,GETDATE()
FROM dbo.Base_Organize bo
WHERE LEN(_PK)>0 AND NOT EXISTS(
	SELECT 1 FROM dbo.Base_AuthorizeData ba
	WHERE ba.ResourceId=bo.OrganizeId AND AuthorizeType=-5 AND ba.Category=5
)

Go

--诊所默认星期一到星期五工作
INSERT INTO dbo.ClinicWorkingSetting
        (Id,ClinicId,StartTime,EndTime,Interval,CreateTime,
         WorkingWeek,NotCareNoShowTime,AppointmentStart,AppointmentEnd,
         CancelNumber)
SELECT NEWID(),bo.OrganizeId,dbo.F_GetTimeFromInt(bo.Z__START_AT),dbo.F_GetTimeFromInt(bo.Z__END_AT)
,bo.Z__INTERVAL,GETDATE(),1,4,4,14,1
FROM dbo.Base_Organize bo
WHERE LEN(bo.[_PK])>0
AND Z__START_AT IS NOT NULL
AND NOT EXISTS(
	SELECT 1 FROM dbo.ClinicWorkingSetting cws
	WHERE cws.ClinicId=bo.OrganizeId
)
UNION ALL
SELECT NEWID(),bo.OrganizeId,dbo.F_GetTimeFromInt(bo.Z__START_AT),dbo.F_GetTimeFromInt(bo.Z__END_AT)
,bo.Z__INTERVAL,GETDATE(),2,4,4,14,1
FROM dbo.Base_Organize bo
WHERE LEN(bo.[_PK])>0
AND Z__START_AT IS NOT NULL
AND NOT EXISTS(
	SELECT 1 FROM dbo.ClinicWorkingSetting cws
	WHERE cws.ClinicId=bo.OrganizeId
)
UNION ALL
SELECT NEWID(),bo.OrganizeId,dbo.F_GetTimeFromInt(bo.Z__START_AT),dbo.F_GetTimeFromInt(bo.Z__END_AT)
,bo.Z__INTERVAL,GETDATE(),3,4,4,14,1
FROM dbo.Base_Organize bo
WHERE LEN(bo.[_PK])>0
AND Z__START_AT IS NOT NULL
AND NOT EXISTS(
	SELECT 1 FROM dbo.ClinicWorkingSetting cws
	WHERE cws.ClinicId=bo.OrganizeId
)
UNION ALL
SELECT NEWID(),bo.OrganizeId,dbo.F_GetTimeFromInt(bo.Z__START_AT),dbo.F_GetTimeFromInt(bo.Z__END_AT)
,bo.Z__INTERVAL,GETDATE(),4,4,4,14,1
FROM dbo.Base_Organize bo
WHERE LEN(bo.[_PK])>0
AND Z__START_AT IS NOT NULL
AND NOT EXISTS(
	SELECT 1 FROM dbo.ClinicWorkingSetting cws
	WHERE cws.ClinicId=bo.OrganizeId
)
UNION ALL
SELECT NEWID(),bo.OrganizeId,dbo.F_GetTimeFromInt(bo.Z__START_AT),dbo.F_GetTimeFromInt(bo.Z__END_AT)
,bo.Z__INTERVAL,GETDATE(),5,4,4,14,1
FROM dbo.Base_Organize bo
WHERE LEN(bo.[_PK])>0
AND Z__START_AT IS NOT NULL
AND NOT EXISTS(
	SELECT 1 FROM dbo.ClinicWorkingSetting cws
	WHERE cws.ClinicId=bo.OrganizeId
)
Go


IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('QueueAttendee') AND name='_PK')
	ALTER TABLE QueueAttendee 
	ADD _PK NVARCHAR(400) NULL
GO

IF EXISTS(SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('AppointmentMain') AND name='Z__CLINICCODE')
INSERT INTO dbo.QueueAttendee
        (Id,ClinicId,QueueManageId,PatientMainId,Type,Date,QueueTime,
         QueueOrder,Status,CallTime,CreateTime,
         AppointmentId,Express,CaseId,
         Source,Enabled,_PK)
SELECT NEWID(),ClinicId,'',PatientMainId,1,Date,CreateTime,
99,2,CreateTime,CreateTime,Id,0,Z__P_CODE+Z__CLINICCODE+Z__DATE
,3,1,_PK
FROM dbo.AppointmentMain am
WHERE LEN(_PK)>0
AND NOT EXISTS(
	SELECT 1 FROM dbo.QueueAttendee q
	WHERE q._PK=am._PK
)
GO


DECLARE _CUR CURSOR READ_ONLY STATIC FOR
SELECT t.name,c.name
FROM sys.columns c 
JOIN sys.tables t ON t.object_id = c.object_id
JOIN sys.objects o ON o.object_id=t.object_id
WHERE o.schema_id=1
AND c.system_type_id=35

DECLARE @Table NVARCHAR(200),@Column NVARCHAR(200),@Sql NVARCHAR(MAX)
OPEN _CUR
WHILE(1=1)
BEGIN
    FETCH NEXT FROM _CUR INTO @Table,@Column;
	IF(@@FETCH_STATUS!=0)BREAK;
	SET @Sql='ALTER TABLE dbo.['+@Table+']'
	+CHAR(13)+CHAR(10)+'ALTER COLUMN '+@Column+' NVARCHAR(MAX) NULL'
	+CHAR(13)+CHAR(10)
	EXECUTE sp_executesql @Sql
END
CLOSE _CUR
DEALLOCATE _CUR
GO



IF NOT EXISTS (SELECT * FROM sys.columns WHERE OBJECT_ID('QueueAttendee')=object_id AND name='_PK')
	ALTER TABLE QueueAttendee 
	ADD _Pk NVARCHAR(500) NULL
GO


IF EXISTS(SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('AppointmentMain') AND name='Z__CLINICCODE')
INSERT INTO dbo.QueueAttendee
        (Id,ClinicId,QueueManageId,PatientMainId,Type,Date,QueueTime,
         QueueOrder,Status,CreateTime,
         AppointmentId,Express,CaseId,
         Source,Enabled,_PK)
SELECT NEWID(),ClinicId,ISNULL((
	SELECT TOP 1 UserId FROM dbo.DoctorMain 
	WHERE Z__DOCTORCODE=ir.Z__DOCTORCODE
),'NUL'),ir.PatientMainId,0,ir.Z__DATE,dbo.F_GetFormatDate(ir.Z__TIMESTAMP,'HH:mm')
,999,3,ir.Z__TIMESTAMP,(
	SELECT TOP 1 Id FROM dbo.AppointmentMain am
	WHERE am.Z__DATE=ir.Z__DATE
	AND am.Z__P_CODE=ir.Z__P_CODE
	--AND am.Z__CLINICCODE=ir.Z__CLINICCODE
	--AND am.Z__DOCTORCODE=ir.Z__DOCTORCODE
),0,ir.CaseId,2,1,ir.CaseId
FROM dbo.InterrogationReports ir
WHERE NOT EXISTS(
	SELECT 1 FROM dbo.QueueAttendee qa
	WHERE qa.CaseId=ir.CaseId
)
AND LEN(ir.Z__TIMESTAMP)>0

GO

IF EXISTS(SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('BillInfo') AND name='Z__CLINICCODE')
INSERT INTO dbo.QueueAttendee
        (Id,ClinicId,QueueManageId,PatientMainId,Type,Date,QueueTime,
         QueueOrder,Status,CreateTime,
         AppointmentId,Express,CaseId,
         Remark,Source,Enabled,
         _Pk)
SELECT NEWID(),ISNULL((
	SELECT TOP 1 bo.OrganizeId FROM dbo.Base_Organize bo
	WHERE bo.EnCode=b.Z__CLINICCODE
),'NUL'),ISNULL((
	SELECT TOP 1 id FROM dbo.Base_User bu
	WHERE bu.EnCode=b.Z__DOCTORCODE
),'NUL'),b.PatientId,0,b.Z__DATE,b.BillTime,
99,5,b.BillTime,(
	SELECT TOP 1 Id FROM dbo.AppointmentMain am
	WHERE am.Z__P_CODE+am.Z__CLINICCODE+am.Z__DATE=b.CaseId
),0,b.CaseId,b.Remark,2,1,b.CaseId
FROM dbo.BillInfo b
WHERE NOT EXISTS(
	SELECT 1 FROM dbo.QueueAttendee qa
	WHERE qa.CaseId=b.CaseId
)
AND LEN(b.Z__TIMESTAMP)>0

GO


DECLARE _CUR CURSOR STATIC READ_ONLY FOR
SELECT Id FROM QueueAttendee 
WHERE AppointmentId IS NULL OR AppointmentId='NUL'
DECLARE @QId NVARCHAR(200) ,@AId NVARCHAR(200),@DId NVARCHAR(200),@DCode NVARCHAR(200)
OPEN _CUR
WHILE(1=1)
BEGIN
    FETCH NEXT FROM _CUR INTO @QId
	IF(@@FETCH_STATUS!=0)
	BREAK;
	SET @AId=NEWID()
	SET @DCode=(SELECT TOP 1 irp.Z__DOCTORCODE 
		FROM dbo.QueueAttendee q
		JOIN dbo.InterrogationReports irp ON irp.CaseId=q.CaseId
		WHERE q.Id=@QId)
	IF(@DCode IS NULL OR LEN(@DCode)=0)
		CONTINUE;
	SET @DId=(SELECT TOP 1 bu.UserId 
		FROM dbo.Base_User bu 
		WHERE bu.EnCode=@DId
		);

	IF(@DId IS NULL)
	BEGIN	    
		SET @DId=NEWID();
		INSERT INTO dbo.Base_User
		        (UserId,EnCode,Account,RealName,NickName,
		         DeleteMark,EnabledMark,English_Name
		         )
		SELECT @DId,@DCode,@DCode,@DCode,@DCode,
			1,0,@DCode
	END

	INSERT dbo.AppointmentMain
	        (Id,ClinicId,Type,PatientMainId,ServiceId,Date,StartTime,Interval,
	         Status,CreateTime,CreatePerson,
	         IsNoShow,IsDelete,SubServiceId,ServiceItem,AppointmentWay,DoctorId,
	         EnCode)
	SELECT @AId,ClinicId,(
		SELECT TOP 1 ItemDetailId FROM dbo.Base_DataItemDetail
		WHERE ItemValue='Consultation'
	),q.PatientMainId,@DId,q.Date,'10:00',15,2,q.CreateTime,q.CreatePerson,
	0,0,
	(
		SELECT TOP 1 d1.ItemDetailId FROM dbo.Base_DataItemDetail d
		JOIN Base_DataItemDetail d1 ON d1.ParentId=d.ItemDetailId
		WHERE d.ItemValue='Consultation'
	),'',3,@DId,q.CaseId
	FROM dbo.QueueAttendee q
	WHERE Id=@QId;
END
CLOSE _CUR
DEALLOCATE _CUR


IF EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('MedicinePreparation') AND max_length<=100 AND name='MaterialName')
	ALTER TABLE MedicinePreparation
	ALTER COLUMN MaterialName NVARCHAR(200) NULL
GO
IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('MedicinePreparation') AND name='Z__CaseId')
	ALTER TABLE MedicinePreparation
	ADd Z__CaseId NVARCHAR(200) NULL
GO
IF EXISTS(SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('MedicinePreparation') AND name='Z__CLINICCODE')
INSERT INTO dbo.MedicinePreparation
        (Id,QueueAttendeeId,EnCode,MaterialId,MaterialName,PackingUnitId,
         PackingNumber,CurrencyUnit,UnitPrice,TotalPrice,UseId,DaysOfTaking,
         TimesPerDay,EachDose,EachDoseUnit,AppointmentTotal,TakingRemark,
         DeliveryAmoun,IsCheck,Remark,CreateDate,CreateUserId,CreateUserName,
         Duration)
SELECT NEWID() AS Id, ISNULL((
	SELECT TOP 1 Id FROM dbo.QueueAttendee q
	WHERE q.CaseId=x.CaseId
),'NUL') AS QueueAttendeeId,x.CaseID,x.MaterialId,x.MaterialName,x.PackingUnitId
,x.DemandNum,x.Currency,x.UnitPrice,x.TotalPrice,x.UseId,x.DaysOfTaking
,x.TimesPerDay,x.EachDose,x.EachDoseUnit,x.TotalQty,x.TakingRemark
,x.DemandNum,0,x.Remark,GETDATE(),x.CreateUserId,x.CreateUserName,1
FROM ActionPrescriptionRecord x
WHERE LEN(x.Z__CLINICCODE)>0
AND  NOT EXISTS(
	SELECT 1 FROM dbo.MedicinePreparation x1
	WHERE x1.Z__CaseId=x.CaseID
)

GO

DECLARE @ServiceTypeId NVARCHAR(200),@SubServiceTypeId NVARCHAR(200)
SET @ServiceTypeId=(
	  SELECT TOP 1 did.ItemDetailId AS Id
	  FROM dbo.Base_DataItem bdi
	  JOIN dbo.Base_DataItemDetail did ON did.ItemId=bdi.ItemId
	  WHERE bdi.ItemCode='ServiceType' AND did.EnabledMark=1 AND did.ItemValue='Nurse'
)

SET @SubServiceTypeId=(
	  SELECT TOP 1 d2.ItemDetailId
	  FROM dbo.Base_DataItem bdi
	  JOIN dbo.Base_DataItemDetail did ON did.ItemId=bdi.ItemId
	  JOIN Base_DataItemDetail d2 ON d2.ParentId=did.ItemDetailId
	  WHERE bdi.ItemCode='ServiceType' 
	  AND did.EnabledMark=1 AND did.ItemValue='Nurse'
	  AND d2.LaboratoryOrVaccine=2
)

UPDATE dbo.AppointmentMain
SET ServiceId=@ServiceTypeId
,SubServiceId=@SubServiceTypeId
WHERE EXISTS(
	SELECT 1 FROM CS6.dbo.PATIENTIMMUNIZATIONTABLE t
	WHERE t.P_CODE =Z__P_CODE COLLATE Chinese_PRC_CI_AS 
	AND t.DATE=Z__DATE COLLATE Chinese_PRC_CI_AS 
	AND t.CLINICCODE=Z__CLINICCODE COLLATE Chinese_PRC_CI_AS
)
GO


UPDATE dbo.AppointmentMain
SET Date=dbo.F_GetFormatDate(Date,'yyyy-MM-dd')
WHERE LEN(Date)=8

UPDATE dbo.Material
SET TypeName=(
	SELECT TOP 1 itemname FROM dbo.Base_DataItemDetail
	WHERE ItemDetailId='bbf7ff1b-cee4-436f-8254-4a96efed6f23'
)
WHERE TypeName LIKE '%?%'

UPDATE dbo.PatientMain
SET Enabled=1
WHERE Enabled IS NULL
GO


DECLARE @QId NVARCHAR(200),@CaseId NVARCHAR(200),@AId NVARCHAR(200);
DECLARE _CUR CURSOR STATIC READ_ONLY FOR
SELECT q.CaseId,q.Id 
FROM dbo.QueueAttendee q
WHERE AppointmentId IS NULL AND LEN(CASEId)>0
OPEN _CUR 
WHILE(1=1)
BEGIN
    FETCH NEXT FROM _CUR INTO @CaseId,@QId;
	IF(@@FETCH_STATUS!=0)BREAK;
	SET @AId=NEWID();
	INSERT INTO dbo.AppointmentMain
        (Id,ClinicId,Type,PatientMainId,ServiceId,Date,StartTime,Interval,
         Status,CreateTime,CreatePerson,Memo,
         IsNoShow,IsDelete,DoctorId,_PK)
	SELECT @AId,qa.ClinicId,'',qa.PatientMainId,'',qa.Date,qa.CreateTime,15,
	3,qa.CreateTime,qa.CreatePerson,qa.MEMO,
	0,0,'恢复数据完整性',qa.CaseId
	FROM dbo.QueueAttendee qa
	WHERE qa.Id=@QId

	UPDATE dbo.QueueAttendee
	SET AppointmentId=@AId
	WHERE Id=@QId
END
CLOSE _CUR
DEALLOCATE _CUR
GO